import requests
import json
# import numpy as np
import pymysql
import time

currentPage = 1
totalPage = 10


base_url = "https://zb.oschina.net/project/contractor-browse-project-and-reward"

db = pymysql.connect(host='localhost', port=3306, user='admin', password='1.1.uP', db='zhongbao', charset="utf8")


def save_to_mysql(data):
    try:
        cursor = db.cursor()
        for item in data:                
            insert_sql = " insert into projects(projectNo, name, skills) values ('%s', '%s', '%s')"
            update_sql = " update projects set skills = '%s' where projectNo = '%s'"
            skills = ''
            for skill in item['skillList']:
                skills += skill['value']+','

            if(search_from_mysql('projectNo', item['projectNo'])) :
                cursor.execute(update_sql % (skills, item['projectNo']))
                print('update to mysql  成功')
            else:
                if item['projectNo'] == "None":
                    pjNo = "yzy%d" % int(time.time() * 1000)
                else:
                    pjNo = item['projectNo']
                cursor.execute(insert_sql % (pjNo, item['name'], skills))
                print('save to mysql  成功')


        db.commit()
        cursor.close()
    except Exception as e:
        print("wrong!!!!!!!!")
        print(e)

def search_from_mysql(column, target):
    try:
        cursor = db.cursor()
        search_sql = "select %s from projects where %s = '%s'" %(column, column, target)
        cursor.execute(search_sql)
        results = cursor.fetchall()
        if(len(results) > 0):
            return True
        else:
            return False
        cursor.close()
        db.close()
    except Exception as e:
        print("search fail~~~~~~~~~")
        print(e)

def spider_body(page):
    global totalPage
    global currentPage
    nowTimeStamp = time.time()
    nowTime = time.strftime("%Y-%m-%d+%H:%M:%S", time.localtime(nowTimeStamp))
    param = {
        "applicationAreas": "",
        "sortBy": 30,
        "currentTime": nowTime,
        "pageSize": 10,
        "currentPage": page
    }
    if( currentPage <= totalPage):
        res = requests.get(base_url, params=param)
        datas = list(json.loads(res.text)['data']['data'])
        totalPage = json.loads(res.text)['data']['totalPage']
        currentPage += 1

        save_to_mysql(datas)

        time.sleep(1)

        spider_body(currentPage)
    else:
        db.close()

def use_data(table, item, value):
    try:
        cursor = db.cursor()
        search_sql = "select %s  from %s where %s like  '%%%s%%';" % (item, table, item, value)
        cursor.execute(search_sql)
        results = cursor.fetchall()
        count = len(results)
        print(count)
        cursor.close()
    except Exception as e:
        print("use data search wrong")
        print(e)

spider_body(currentPage)

# use_data('projects', 'skills', 'python')
# use_data('projects', 'skills', 'java')
# use_data('projects', 'skills', 'node.js')
# use_data('projects', 'skills', 'php')
# use_data('projects', 'skills', 'javascript')
# use_data('projects', 'skills', 'vue')
# use_data('projects', 'skills', 'react')

